

var DATABASE = 'EX5';

var USER_TABLE = 'User';
var BOARD_TABLE = 'Board';
var USER_JOINED_BOARD_TABLE = 'User_joined_Board';
var MESSAGE_TABLE = 'Message';
var CATEGORY_TABLE = 'Category';



var client;

exports.initDBClient=function (dbClient){

    client=dbClient;

}

exports.stopDBClient = function () {

    client.end();
}

exports.queryUser = function (username, password, response, cb) {

    client.query(
      'SELECT * FROM ' + USER_TABLE + ' WHERE username=\"' + username + '\"',
    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
           // throw err;
        }
        var firstResult = results[0];
        cb(firstResult, response);
    }
    );

}


exports.setUser = function (username, password, name, picture,  cb) {

    client.query(
   'INSERT INTO ' + USER_TABLE + ' ' +
  'SET username = ?, password = ?, name = ?,picture= ?',
  [username, password, name, picture],
   function selectCb(err, results, fields) {
       if (err) {
           var msg = 'GetData Error: ' + err.message;
           cb(firstResult, msg);
           return;
       }
       var firstResult = {
           username: username,
           name: name,
           picture: picture
       };

       cb(firstResult,  msg);
   }
    );

}



exports.queryBoardsByUser = function (username, request,response, cb) {

  

    client.query(
      'SELECT *,C.Name AS category,Count(message.board_name) as size FROM ' + BOARD_TABLE + ' B ' +
 ' JOIN ' + USER_JOINED_BOARD_TABLE + ' J ON B.name= j.board_name' +
 ' JOIN  '+CATEGORY_TABLE+ ' C ON B.category_id=c.id  '+
 'LEFT OUTER JOIN ' + MESSAGE_TABLE + ' ON B.name = message.board_name ' + 
 ' WHERE J.username=\"' + username + '\"'+
 'GROUP BY B.name',


    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
           // throw err;
        }



        cb(results,request, response);
    }
    );

}



exports.getBoardUserPermission = function (boardname,username, response, cb) {

    client.query(
      'SELECT board_name FROM ' + USER_JOINED_BOARD_TABLE +
      ' WHERE username=\"' + username + '\"  AND board_name=\"' + boardname + '\"',
    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
          //  throw err;
        }



        cb(results[0], response);
    }
    );

}


exports.queryCategorys = function ( response, cb) {
  
  
    client.query(
      'SELECT * FROM ' + CATEGORY_TABLE ,
    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
          //  throw err;
        }

        cb(results, response);
    }
    );

}


exports.setBoard = function (ntitle, ncategory, nboardname, nusername, response, cb) {

   
        client.query(
   'INSERT INTO ' + BOARD_TABLE + ' ' +
  'SET name = ?, title = ?, category_id = ?,owner_username= ?',
  [nboardname, ntitle, ncategory, nusername, ], function (error, results) {

      if (error) {
          var msg = 'GetData Error: ' + error.message;
          cb(undefined, msg, response);
          return;
      }
      
    
     setUserJoinedBoard(nusername,nboardname,response,cb);
    
  }
   );


}



exports.setMsg = function (boardName, subject, text, color, username, response, cb) {

    var d = new Date();
    var day = (d.getDate() < 10 ? "0" + d.getDate() : d.getDate());
    var mont = (d.getMonth() + 1 < 10 ? "0" + (d.getMonth() + 1) : d.getMonth() + 1);
    var sqlDate = "" + d.getFullYear() + "-" + mont + "-" + day  + "";
    client.query(
   'INSERT INTO ' + MESSAGE_TABLE + ' ' +
  'SET board_name = ?, Username = ?,Creation_date=?, Title = ?,Text= ?,Color=?',
  [boardName, username, sqlDate, subject, text, color], function (error, results) {

      if (error) {
          var msg = 'GetData Error: ' + error.message;
          cb(undefined, response);
          return;
      }


      cb(boardName, response);

  }
   );


}




exports.deleteMessagesfromBoard = function (boardName, id,  response, cb) {

   client.query(
   'DELETE FROM ' + MESSAGE_TABLE + ' ' +
    'WHERE id=\"' + id + '\" AND board_name=\"' + boardName + '\"',
     function (error, results) {

      if (error) {
          var msg = 'GetData Error: ' + error.message;
          cb(undefined, response);
          return;
      }


      cb(id, response);

  }
   );


}

function setUserJoinedBoard(username,boardname,response,cb){


    client.query(
  'INSERT INTO ' + USER_JOINED_BOARD_TABLE + ' ' +
  'SET username = ?, board_name = ?',
  [username, boardname], function (error, results) {

      if (error) {
          var msg = 'GetData Error: ' + error.message;
          cb(undefined, msg, response);
          return;
      }



      cb(boardname, msg, response);
  });

  
}

exports.setUserJoinedBoard = setUserJoinedBoard;




exports.querySearch = function (username, searchStr, request, response, cb) {

    client.query(
     'SELECT *,C.Name AS category, J.username AS memberName FROM ' + BOARD_TABLE + ' B ' + ' JOIN ' + USER_JOINED_BOARD_TABLE + ' J ON B.name= j.board_name' +
      ' JOIN  ' + CATEGORY_TABLE + ' C ON B.category_id=c.id ' +
      ' WHERE B.Name like \"' + searchStr + '%\"  OR C.Name=\"' + searchStr + '\"  ' ,
    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
         //   throw err;
        }



        cb(results, searchStr, request, response);
    }
    );


}







exports.queryMessagesfromBoard = function (boardname, response, cb) {


    client.query(
      'SELECT * FROM ' + MESSAGE_TABLE +
      ' WHERE board_name=\"' + boardname + '\" AND Deletion_date IS  NULL',

    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
        //    throw err;
        }



        getBoardbyName(boardname, results, response, cb);
    }
    );

}


function getBoardbyName(boardname,msgs, response, cb) {


    client.query(
      'SELECT *, C.Name AS category  FROM ' + BOARD_TABLE + ' B ' +
      ' JOIN  ' + CATEGORY_TABLE + ' C ON B.category_id=c.id ' +
      'WHERE B.Name=\"' + boardname + '\" ',

    function selectCb(err, results, fields) {
        if (err) {
            console.log('GetData Error: ' + err.message);
          //  throw err;
        }

        cb(results[0], msgs, response);
    }
    );


}

